﻿' ------------------------------------------------------------------------------
' The SpatialQueryBuilderControl lets the user select a geographic place
' by name (from a list) or by clicking on the map, and then uses the
' place's geography as the basis for a spatial database query. The user
' can also select a buffer distance around the place to enlarge the area
' that captures the photos.
'
' At the time of writing, the control supports three geographic
' placename sources or layers: City (points), County, and State (both
' polygons). Information about each layer is stored in a PlaceNameSpec
' object. These are created by the static LoadSpecs() method. When the
' user selects a layer to use, the layer's spec is loaded into m_spec
' which is then used throughout the code. Two members of PlaceNameSpec
' are delegates: DisplayString() returns a string to display in the
' combobox of placenames, and BuildQuery() returns the SQL query
' for quering the geography column from the database.
'
' When the user selects a layer, the basemap layers in the Dundas map
' control are turned on/off, then the data is loaded into the placename
' combobox.
'
' If the user double-clicks on the map while a polygon layer is
' selected, a spatial search will be performed to find and load the
' polygon beneath the mouse cursor.
' ------------------------------------------------------------------------------
' This file is part of the SQL Server 2008 Technology Demonstration. This
' application was designed and coded by Eric Weitzman, LandTime Inc, for
' Microsoft Corporation. Contact Ed Katibah at Microsoft about this demo.
' Copyright © 2008 Microsoft Corporation. All rights reserved.
' ------------------------------------------------------------------------------

Imports WinForms = System.Windows.Forms

Public Class SpatialQueryBuilderControl

    Private Delegate Function DlgStringFromRow(ByVal row As DataRow) As String
    Public Event SqlQueryChanged(ByVal sql As String, ByVal queryname As String)

    Private Class PlaceNameSpec
        Public Table As String
        Public SpatialColumn As Integer
        Public Sql As String
        Public FirstHit As String
        Public DataTable As DataTable = Nothing
        Public Auto As WinForms.AutoCompleteStringCollection = Nothing
        Public DisplayString As DlgStringFromRow
        Public BuildQuery As DlgStringFromRow
    End Class

    Private m_map As DundasMapVeneer
    Private Shared m_specs As Dictionary(Of String, PlaceNameSpec)
    Private m_spec As PlaceNameSpec

    Private WithEvents m_cboPlaceName As WinForms.ComboBox

    Const DUNDAS_MAP As Boolean = True
    Const WINFORMS_COMBOBOX As Boolean = True

    Private Sub SpatialQueryBuilder_Loaded(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Loaded

        If System.ComponentModel.DesignerProperties.GetIsInDesignMode(Me) Then Return

        If m_cboPlaceName IsNot Nothing Then Return ' use this variable to tell us if already loaded

        If WINFORMS_COMBOBOX Then
            m_cboPlaceName = WindowsFormsHost2.Child
            m_cboPlaceName.Height = WindowsFormsHost2.ActualHeight - 10
            m_cboPlaceName.Width = WindowsFormsHost2.ActualWidth - 10
            m_cboPlaceName.DropDownStyle = WinForms.ComboBoxStyle.Simple
        End If

        LoadSpecs()

        If DUNDAS_MAP Then
            m_map = New DundasMapVeneer(WindowsFormsHost1.Child)
            StartMapUpdate()
            m_map.LoadMap(False)
            If g_connection.Connected Then
                For Each tablename As String In New String() {My.Settings.TABLE_State, My.Settings.TABLE_County}
                    Dim spec As PlaceNameSpec = m_specs.Item(tablename)
                    Dim cmd As SqlCommand = g_connection.CreateCommand()
                    cmd.CommandText = spec.Sql
                    Dim dt As DataTable = GetDataTable(tablename, cmd, g_connection)
                    m_map.LoadLayer(tablename & "Basemap", dt, spec.SpatialColumn)
                Next
                m_map.HideLayer(My.Settings.TABLE_County & "Basemap")
                m_map.SetMapExtent(DundasMapVeneer.EnumExtent.eCONUS)
            End If
            EndMapUpdate()
        End If

        rdoCityPoint.IsChecked = False
        rdoCounty.IsChecked = False
        rdoMetro.IsChecked = False
        rdoState.IsChecked = True

    End Sub

#Region " PlaceNameSpecs "
    Private Shared Sub LoadSpecs()
        m_specs = New Dictionary(Of String, PlaceNameSpec)
        Dim table As String
        Dim sql As String

        table = My.Settings.TABLE_City
        sql = String.Format("SELECT City, ST, Location FROM {0} ORDER BY City", table)
        m_specs.Add(table, New PlaceNameSpec With {.Table = table, _
                                                   .Sql = sql, _
                                                   .SpatialColumn = 2, _
                                                   .BuildQuery = New DlgStringFromRow(AddressOf BuildCityQuery), _
                                                   .DisplayString = New DlgStringFromRow(AddressOf DisplayStringAppendFirstTwoColumns)})
        table = My.Settings.TABLE_County
        Dim TABLE_State As String = My.Settings.TABLE_State
        sql = String.Format("SELECT C.County, S.ST AS ST, C.Location AS Location FROM {0} AS C, {1} AS S WHERE C.State = S.State ORDER BY County", table, TABLE_State)
        m_specs.Add(table, New PlaceNameSpec With {.Table = table, _
                                                   .Sql = sql, _
                                                   .SpatialColumn = 2, _
                                                   .BuildQuery = New DlgStringFromRow(AddressOf BuildCountyQuery), _
                                                   .DisplayString = New DlgStringFromRow(AddressOf DisplayStringAppendFirstTwoColumns)})
        table = My.Settings.TABLE_State
        sql = String.Format("SELECT State, ST, Location from {0} ORDER BY State", table)
        m_specs.Add(table, New PlaceNameSpec With {.Table = table, _
                                                   .Sql = sql, _
                                                   .SpatialColumn = 2, _
                                                   .BuildQuery = New DlgStringFromRow(AddressOf BuildStateQuery), _
                                                   .DisplayString = New DlgStringFromRow(AddressOf DisplayStringFirstColumn)})
        table = My.Settings.TABLE_Metro
        sql = String.Format("SELECT M.MetroName, S.ST AS ST, M.Location AS Location FROM {0} AS M, {1} AS S WHERE M.State = S.State ORDER BY MetroName", table, TABLE_State)
        m_specs.Add(table, New PlaceNameSpec With {.Table = table, _
                                                   .Sql = sql, _
                                                   .SpatialColumn = 4, _
                                                   .BuildQuery = New DlgStringFromRow(AddressOf BuildMetroQuery), _
                                                   .DisplayString = New DlgStringFromRow(AddressOf DisplayStringAppendFirstTwoColumns)})
    End Sub

    Private Shared Function BuildCityQuery(ByVal row As DataRow) As String
        Return String.Format("Location FROM {0} WHERE City = '{1}' and ST = '{2}'", _
                             My.Settings.TABLE_City, row.Item(0).ToString, row.Item(1).ToString)
    End Function

    Private Shared Function BuildCountyQuery(ByVal row As DataRow) As String
        Return String.Format("C.Location FROM {0} AS C, {1} AS S WHERE C.County = '{2}' AND C.State = S.State and S.ST = '{3}'", _
                             My.Settings.TABLE_County, My.Settings.TABLE_State, row.Item(0).ToString, row.Item(1).ToString)
    End Function

    Private Shared Function BuildMetroQuery(ByVal row As DataRow) As String
        Return String.Format("M.Location FROM {0} AS M, {1} AS S WHERE M.MetroName = '{2}' AND M.State = S.State and S.ST = '{3}'", _
                             My.Settings.TABLE_Metro, My.Settings.TABLE_State, row.Item(0).ToString, row.Item(1).ToString)
    End Function

    Private Shared Function BuildStateQuery(ByVal row As DataRow) As String
        Return String.Format("Location FROM {0} WHERE ST = '{1}'", _
                             My.Settings.TABLE_State, row.Item(1).ToString)
    End Function

    Private Shared Function DisplayStringAppendFirstTwoColumns(ByVal row As DataRow) As String
        Return DirectCast(row.Item(0), String).Trim & ", " & DirectCast(row.Item(1), String).Trim
    End Function

    Private Shared Function DisplayStringFirstColumn(ByVal row As DataRow) As String
        Return DirectCast(row.Item(0), String).Trim
    End Function
#End Region

    Private Sub rdoState_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rdoState.Checked
        If m_map Is Nothing Then Return
        If DUNDAS_MAP Then m_map.ClearLayer(My.Settings.TABLE_State)
        If Not rdoState.IsChecked Then Return
        If DUNDAS_MAP Then
            m_map.HideLayer(My.Settings.TABLE_County)
            m_map.HideLayer(My.Settings.TABLE_City)
            m_map.HideLayer(My.Settings.TABLE_County & "Basemap")
            m_map.ShowLayer(My.Settings.TABLE_State & "Basemap")
            m_map.ClearLayer("InPhoto")
            m_map.ClearLayer("OutPhoto")
            m_map.ShowLayer(My.Settings.TABLE_State)
        End If
        LoadData(My.Settings.TABLE_State)
    End Sub

    Private Sub rdoCityPoint_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles rdoCityPoint.Checked
        If m_map Is Nothing Then Return
        If DUNDAS_MAP Then m_map.ClearLayer(My.Settings.TABLE_City)
        If Not rdoCityPoint.IsChecked Then Return
        If DUNDAS_MAP Then
            m_map.HideLayer(My.Settings.TABLE_County)
            m_map.HideLayer(My.Settings.TABLE_State)
            m_map.ShowLayer(My.Settings.TABLE_County & "Basemap")
            m_map.ShowLayer(My.Settings.TABLE_State & "Basemap")
            m_map.ClearLayer("InPhoto")
            m_map.ClearLayer("OutPhoto")
            m_map.ShowLayer(My.Settings.TABLE_City)
        End If
        LoadData(My.Settings.TABLE_City)
    End Sub

    Private Sub rdoCounty_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles rdoCounty.Checked
        If m_map Is Nothing Then Return
        If DUNDAS_MAP Then m_map.ClearLayer(My.Settings.TABLE_County)
        If Not rdoCounty.IsChecked Then Return
        If DUNDAS_MAP Then
            m_map.HideLayer(My.Settings.TABLE_State)
            m_map.HideLayer(My.Settings.TABLE_City)
            m_map.HideLayer(My.Settings.TABLE_State & "Basemap")
            m_map.ShowLayer(My.Settings.TABLE_County & "Basemap")
            m_map.ClearLayer("InPhoto")
            m_map.ClearLayer("OutPhoto")
            m_map.ShowLayer(My.Settings.TABLE_County)
        End If
        LoadData(My.Settings.TABLE_County)
    End Sub

    Private Sub rdoMetro_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles rdoMetro.Checked
        If m_map Is Nothing Then Return
        If DUNDAS_MAP Then m_map.ClearLayer(My.Settings.TABLE_Metro)
        If Not rdoMetro.IsChecked Then Return
        If DUNDAS_MAP Then
            m_map.HideLayer(My.Settings.TABLE_State)
            m_map.HideLayer(My.Settings.TABLE_City)
            m_map.HideLayer(My.Settings.TABLE_State & "Basemap")
            m_map.ShowLayer(My.Settings.TABLE_County & "Basemap")
            m_map.ClearLayer("InPhoto")
            m_map.ClearLayer("OutPhoto")
            m_map.ShowLayer(My.Settings.TABLE_County)
        End If
        LoadData(My.Settings.TABLE_Metro)
    End Sub

    Private Sub LoadData(ByVal tablename As String)

        Assert(m_specs.ContainsKey(tablename), "missing placename spec for table: " & tablename)
        m_spec = m_specs.Item(tablename)

        Try
            ' load the table from the database and populate the combobox and 
            ' its autocomplete string collection. these are cached in the spec

            If m_spec.DataTable Is Nothing Then
                Dim cmd As SqlCommand = g_connection.CreateCommand()
                cmd.CommandText = m_spec.Sql
                m_spec.DataTable = GetDataTable(m_spec.Table, cmd, g_connection)
                m_spec.Auto = New WinForms.AutoCompleteStringCollection
                ReportingUtils.StartTimer()
                For Each row As DataRow In m_spec.DataTable.Rows
                    m_spec.Auto.Add(m_spec.DisplayString.Invoke(row))
                Next
                ReportingUtils.ShowTimer("loaded autocomplete list")
            End If

            If WINFORMS_COMBOBOX Then

                If m_spec.DataTable.Rows.Count > 10000 Then
                    m_cboPlaceName.DataSource = Nothing
                    m_cboPlaceName.Items.AddRange(New String() {"", "    loading " & m_spec.DataTable.Rows.Count & " rows..."})
                End If

                m_cboPlaceName.BeginUpdate()

                m_ignore_change = True
                m_cboPlaceName.DataSource = m_spec.Auto
                m_ignore_change = False

                m_cboPlaceName.AutoCompleteSource = WinForms.AutoCompleteSource.CustomSource
                m_cboPlaceName.AutoCompleteMode = WinForms.AutoCompleteMode.Append
                m_cboPlaceName.AutoCompleteCustomSource = m_spec.Auto

                ' position list to previously-selected value

                If m_spec.FirstHit = "" Then
                    m_cboPlaceName.SelectedIndex = -1
                Else
                    m_cboPlaceName.SelectedItem = m_spec.FirstHit
                End If

                m_cboPlaceName.EndUpdate()
            End If

            If m_spec.Table = My.Settings.TABLE_City Then
                nudExpandBy.Text = 1
            Else
                nudExpandBy.Text = 0
            End If
            'If nudExpandBy.Value < nudExpandBy.Minimum Then nudExpandBy.Value = nudExpandBy.Minimum


            If DUNDAS_MAP Then
                If m_cboPlaceName.SelectedIndex <> -1 Then
                    Dim row As DataRow = m_spec.DataTable.Rows.Item(m_cboPlaceName.SelectedIndex)
                    Dim loc As SqlGeography = DirectCast(row.Item(2), SqlGeography)
                    StartMapUpdate()
                    ShowSelectedGeography(loc)
                    'txtSql.Text = MakeSpatialQuery(row)
                    'ShowPhotos(row)
                    EndMapUpdate()
                End If
            End If

        Catch ex As Exception
            ReportException(ex)
        End Try
    End Sub

    Private Sub btnAddToQuery_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddToQuery.Click
        Dim queryname As String = ""
        Dim i As Integer = m_cboPlaceName.SelectedIndex
        If i <> -1 Then
            Dim row As DataRow = m_spec.DataTable.Rows.Item(i)
            queryname = m_spec.DisplayString.Invoke(row)
            Dim bdist As Integer = Decimal.ToInt32(nudExpandBy.Text)
            If bdist <> 0 Then queryname &= " +" & bdist.ToString & "km"
        End If

        RaiseEvent SqlQueryChanged(txtSQL.Text, queryname)
    End Sub

    Private Sub nudExpandBy_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Input.KeyEventArgs) Handles nudExpandBy.KeyUp
        BufferChanged()
    End Sub
    Private Sub nudExpandBy_ValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles nudExpandBy.TextChanged
        BufferChanged()
    End Sub

    Private m_ignore_change As Boolean = False

    Private Sub m_cboPlaceName_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles m_cboPlaceName.SelectedIndexChanged

        If m_ignore_change Then Return
        Dim i As Integer = m_cboPlaceName.SelectedIndex
        If i < 0 Or i > m_spec.DataTable.Rows.Count Then Return

        Dim row As DataRow = m_spec.DataTable.Rows.Item(i)
        m_spec.FirstHit = m_spec.DisplayString.Invoke(row)

        Dim loc As SqlGeography = DirectCast(row.Item(2), SqlGeography)
        ShowSelectedGeography(loc)
        txtSQL.Text = MakeSpatialQuery(row)

    End Sub

    Private Sub ShowSelectedGeography(ByVal geog As SqlGeography)
        If geog Is Nothing Then Return
        If Not DUNDAS_MAP Then Return

        StartMapUpdate()
        Dim bdist As Integer = Decimal.ToInt32(nudExpandBy.Text)
        Dim buffer As SqlGeography = Nothing
        If bdist <> 0 Then
            If geog.STNumPoints < 100 Then
                buffer = geog.STBuffer(bdist * 1000)
            Else
                buffer = geog.Reduce(1000).STBuffer(bdist * 1000)
                'buffer = geog.STBuffer(bdist * 1000)
            End If
        End If

        If m_spec.Table = My.Settings.TABLE_City Then
            m_map.ShowSqlGeography(m_spec.Table, geog, buffer, m_spec.FirstHit, True)
        Else
            m_map.ShowSqlGeography(m_spec.Table, geog, buffer, "", True)
        End If
        EndMapUpdate()
    End Sub

    Private Function MakeSpatialQuery(ByVal row As DataRow, Optional ByVal columns As String = "*") As String
        Dim sql As String = m_spec.BuildQuery.Invoke(row)
        Dim sb As New System.Text.StringBuilder
        sb.AppendLine("DECLARE @g GEOGRAPHY;")
        sb.Append("SELECT @g = ").Append(sql).AppendLine(";")
        Dim bdist As Integer = Decimal.ToInt32(nudExpandBy.Text)
        If bdist <> 0 Then sb.Append("SET @g = @g.Reduce(1000).STBuffer(").Append(bdist * 1000).AppendLine(");")
        sb.Append("SELECT ").Append(columns)
        sb.Append(" FROM ").Append(My.Settings.TABLE_Photo)
        sb.AppendLine(" WHERE Location.STIntersects(@g) = 1;")
        Return sb.ToString
    End Function

    Private Function MakeExtentQuery(ByVal row As DataRow, Optional ByVal columns As String = "*") As String
        Dim sql As String = m_spec.BuildQuery.Invoke(row)
        Dim sb As New System.Text.StringBuilder
        Dim wktbox As String = m_map.MapExtentAsWKT(g_connection.CoordinateOrder)
        sb.Append("SELECT ").Append(columns)
        sb.Append(" FROM ").Append(My.Settings.TABLE_Photo)
        sb.Append(" WHERE Location.STIntersects('").Append(wktbox).AppendLine("') = 1;")
        Return sb.ToString
    End Function

    Private Sub BufferChanged()
        If m_spec Is Nothing Then Return
        If Not WINFORMS_COMBOBOX Then Return
        Dim i As Integer = m_cboPlaceName.SelectedIndex
        If i < 0 Or i > m_spec.DataTable.Rows.Count Then Return
        Dim row As DataRow = m_spec.DataTable.Rows.Item(i)
        txtSql.Text = MakeSpatialQuery(row)
    End Sub

    Private Sub ShowPhotos(ByVal row As DataRow)
        Dim sql As String = MakeSpatialQuery(row)
        Try
            Dim cmdin As SqlCommand, cmdout As SqlCommand
            Dim dtin As DataTable, dtout As DataTable

            If DUNDAS_MAP Then m_map.ClearLayer("InPhoto")
            If DUNDAS_MAP Then m_map.ClearLayer("OutPhoto")

            cmdout = g_connection.CreateCommand()
            cmdout.CommandText = MakeExtentQuery(row, "Location")
            dtout = GetDataTable(My.Settings.TABLE_Photo, cmdout, g_connection)

            cmdin = g_connection.CreateCommand()
            cmdin.CommandText = MakeSpatialQuery(row, "Location")
            dtin = GetDataTable(My.Settings.TABLE_Photo, cmdin, g_connection)

            If dtin.Rows.Count + dtout.Rows.Count = 0 Then
                MessageBox.Show("No photos found", "", MessageBoxButton.OK, MessageBoxImage.Warning)
                Exit Sub
            End If

            If DUNDAS_MAP Then
                m_map.LoadLayer("OutPhoto", dtout, 0)
                m_map.LoadLayer("InPhoto", dtin, 0)
                m_map.Map.Update()
            End If

        Catch ex As Exception
            ReportException(ex)
        End Try
    End Sub

    Private Sub btnApply_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnApply.Click
        If Not WINFORMS_COMBOBOX Then Return
        Dim i As Integer = m_cboPlaceName.SelectedIndex
        If i < 0 Or i > m_spec.DataTable.Rows.Count Then Return
        Dim row As DataRow = m_spec.DataTable.Rows.Item(i)
        Dim loc As SqlGeography = DirectCast(row.Item(2), SqlGeography)
        StartMapUpdate()
        ShowSelectedGeography(loc)
        txtSQL.Text = MakeSpatialQuery(row)
        ShowPhotos(row)
        EndMapUpdate()
    End Sub

    Dim m_last_text As New Stack(Of String)
    Private Sub StartMapUpdate()
        m_last_text.Push(txtSql.Text)
        txtSql.Text = vbCrLf & "                          updating map..."
    End Sub
    Private Sub EndMapUpdate()
        txtSql.Text = m_last_text.Pop()
    End Sub

    Private Sub MapControl1_MouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Input.MouseEventArgs) Handles WindowsFormsHost1.MouseUp

        If Not DUNDAS_MAP Then Return
        If m_spec.Table = My.Settings.TABLE_City Then Return

        ' get the geography that the user clicked on from SQL server

        Dim pnt As Dundas.Maps.WinControl.MapPoint = m_map.Map.PixelsToGeographic(New System.Drawing.PointF(e.GetPosition(WindowsFormsHost1).X, e.GetPosition(WindowsFormsHost1).Y))
        Dim wktmapclick As String = CreateWktGeographyPoint(pnt.X, pnt.Y, g_connection.CoordinateOrder)

        Dim cmd As SqlCommand = g_connection.CreateCommand()
        cmd.CommandText = String.Format( _
            "SELECT TOP 1 Location FROM {0} WHERE Location.STIntersects(@MapClick) = 1", _
            m_spec.Table, wktmapclick)
        cmd.Parameters.AddWithValue("@MapClick", wktmapclick)
        ReportSqlCommand("spatial select " & m_spec.Table, cmd)
        Dim loc As SqlGeography = CType(cmd.ExecuteScalar(), SqlGeography)

        ShowSelectedGeography(loc)
        For Each row As DataRow In m_spec.DataTable.Rows
            If CType(row.Item(m_spec.SpatialColumn), SqlGeography).STEquals(loc) Then
                txtSQL.Text = MakeSpatialQuery(row)
                If WINFORMS_COMBOBOX Then m_cboPlaceName.SelectedItem = m_spec.DisplayString.Invoke(row)
                Exit For
            End If
        Next
    End Sub

End Class
